const express = require('express');
const { executeQuery, handleError, formatDateTime } = require('../utils');
const router = express.Router();

// 查询年度征订记录，优先使用 next_year_reservations 表中的年、卷、期
router.post('/list', async (req, res) => {
    const { page = 1, pageSize = 10, search = '' } = req.body;

    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    // 查询总记录数（只统计未被删除的记录，支持搜索）
    const totalQuery = `
        SELECT COUNT(*) AS total
        FROM next_year_reservations nr
        JOIN journals j ON nr.journal_id = j.journal_id
        JOIN journaldirectories jd ON j.directory_id = jd.directory_id
        AND (jd.journal_name LIKE ? OR j.year LIKE ?);
    `;

    // 查询年度征订记录（支持搜索，优先使用 next_year_reservations 的年、卷、期字段）
    const query = `
        SELECT 
            nr.reservation_id,
            nr.journal_id,
            jd.journal_name,
            nr.borrow_count,
            nr.zero_stock_count,
            nr.feedback_count,
            nr.isdel,
            -- 优先使用 next_year_reservations 表中的年、卷、期字段，如果为 0 则使用 journals 表中的字段
            CASE 
                WHEN nr.year = 0 THEN j.year 
                ELSE nr.year 
            END AS year,
            CASE 
                WHEN nr.volume = 0 THEN j.volume 
                ELSE nr.volume 
            END AS volume,
            CASE 
                WHEN nr.issue = 0 THEN j.issue 
                ELSE nr.issue 
            END AS issue,
            nr.created_at
        FROM 
            next_year_reservations nr
        JOIN 
            journals j ON nr.journal_id = j.journal_id
        JOIN 
            journaldirectories jd ON j.directory_id = jd.directory_id
        AND 
            (jd.journal_name LIKE ? OR j.year LIKE ?)
        LIMIT ${limit} OFFSET ${offset};
    `;

    try {
        // 模糊搜索条件，支持搜索期刊名称或年份
        const searchParam = `%${search}%`;

        // 获取总记录数
        const totalResult = await executeQuery(totalQuery, [searchParam, searchParam]);
        const total = totalResult[0]?.total || 0;

        // 获取记录
        const records = await executeQuery(query, [searchParam, searchParam]);

        // 返回结果
        res.json({ total, page, pageSize, data: records });
    } catch (error) {
        console.error('查询年度征订记录出错:', error);
        handleError(error, res);
    }
});

// 新增预测
router.post('/add', async (req, res) => {
    const { journal_id, year, volume, issue } = req.body;

    const insertQuery = `
        INSERT INTO next_year_reservations 
        (journal_id, year, volume, issue, borrow_count, zero_stock_count, feedback_count, isdel)
        VALUES (?, ?, ?, ?, 0, 0, 0, -1);
    `;
    try {
        await executeQuery(insertQuery, [journal_id, year, volume, issue]);

        res.json({ message: '年度征订记录新增成功' });
    } catch (error) {
        console.error('新增年度征订记录失败:', error);
        handleError(error, res);
    }
});


// 修改预定状态
router.post('/delete', async (req, res) => {
    const { reservation_id, isdel } = req.body;

    // 更新 isdel 字段，传递 1 或 -1
    const query = 'UPDATE next_year_reservations SET isdel = ? WHERE reservation_id = ?;';

    try {
        await executeQuery(query, [isdel, reservation_id]);
        res.json({ message: '年度征订记录已更新' });
    } catch (error) {
        handleError(error, res);
    }
});

module.exports = router;
